# coding:utf-8

import os
import re
import json
import datetime

from sqlalchemy import or_
import xlwt
from flask import (
    render_template, redirect, url_for, flash, request, current_app, jsonify,
    send_file, abort
)
from flask_login import login_required, current_user
from flask_sqlalchemy import get_debug_queries
from wtforms_components import read_only

from app import encrypt
from app.utils import MsgProxy
from common.constant import BID_USER_COUNT
from .. import db
from ..constant import Choice
from ..forms import (
    NonValidatingSelectField, NotChoices, MyDateTimeField
)
from ..wechat.models import WechatSendLog
from . import op_bp
from .models import (
    User, Demand, Area, Audio, DemandFinishImg, Partic,
    ParticStatus, DemandClient, RenewableDemand, UserVisitDemand
)
from app.ccws.models import OutBoundRecord, create_outbound_mission
from app.ccws.constant import QueueState
from .demand_forms import (
    SearchDemandForm, DetailDemandForm,
    CreateDemandForm, SearchDemandForSemForm,
    PublishDemandForm, SearchBidSupplierForm,
    SearchDemandClientForm, SearchRenewableDemandForm, TrainingDemandForm)
from .constant import (
    DemandCate, DemandMalf, UserType, DemandNoticeWay,
    UserCreditRule, AreaType,
    DemandRentType, DemandType, DemandFinishImgState, FeedbackStatus,
    DemandStatus, ContactState, FollowType,
    OperateResult, OperatedStatus
)

from app.wechat.utils import WX_TEMPLATE_FUNC_MAP
from app.seven_moor.models import SevenMoorRecord
from common.demand_util import demand_winner_count


@op_bp.after_app_request
def after_request(response):
    for query in get_debug_queries():
        if query.duration >= current_app.config['FLASKY_SLOW_DB_QUERY_TIME']:
            current_app.logger.warning(
                'Slow query: %s\nParameters: %s\nDuration: %fs\nContext: %s\n'
                % (query.statement, query.parameters, query.duration,
                   query.context))
    return response


@op_bp.route('/demand_list', methods=['GET', 'POST'])
@login_required
def demand_list():
    form = SearchDemandForm(formdata=request.form or request.args)

    demand_query = Demand.query.filter_by(deleted_at=0)
    url_kwargs = {}
    if form.validate():

        # 如果根据发布者手机号查找，则根据 relationship 查找 Demand
        if form.user_phone.data:
            owner = User.query.filter_by(
                mobile=form.user_phone.data, deleted_at=0).first()
            if owner:
                demand_query = owner.demands
            else:
                demand_query = demand_query.filter_by(uid=0)

        query_kwargs, url_kwargs = form.generate_kwargs()

        # 根据 form 表单的值，构造Demand的query对象
        demand_query = Demand.generate_list_query_condition(
            args=query_kwargs,
            query=demand_query
        )

        if form.has_audio.data:
            demand_query = demand_query.filter(
                Demand.audio != ''
            )

        if form.is_for_gold.data == Choice.YES.value:
            demand_query = demand_query.filter(
                Demand.for_gold.is_(True)
            )
        elif form.is_for_gold.data == Choice.NO.value:
            demand_query = demand_query.filter(
                Demand.for_gold.is_(False)
            )

        if form.ignore_invalid.data:
            demand_query = demand_query.filter(
                Demand.status != DemandStatus.INVALID.value
            )

        if form.robot_win.data:
            robot_win_selected = form.robot_win.data
            if robot_win_selected == 1:
                demand_query = demand_query.filter(
                    Demand.id.in_(
                        Partic.query.filter(
                            Partic.is_robot.is_(True),
                            Partic.is_training.is_(False),
                            Partic.status == ParticStatus.SUCCEED.value
                        ).with_entities(Partic.demand_id)
                    )
                )
            elif robot_win_selected == 2:
                demand_query = demand_query.filter(
                    Demand.id.in_(
                        Partic.query.filter(
                            Partic.is_robot.is_(True),
                            Partic.is_training.is_(False),
                            Partic.status == ParticStatus.SUCCEED.value
                        ).with_entities(Partic.demand_id)
                    ),
                    Demand.id.notin_(
                        Partic.query.filter(
                            Partic.is_robot.is_(False),
                            Partic.is_training.is_(False),
                            Partic.status == ParticStatus.SUCCEED.value
                        ).with_entities(Partic.demand_id)
                    )
                )
            elif robot_win_selected == 3:
                demand_query = demand_query.filter(
                    Demand.id.in_(
                        Partic.query.filter(
                            Partic.is_robot.is_(True),
                            Partic.is_training.is_(False),
                            Partic.status == ParticStatus.SUCCEED.value
                        ).with_entities(Partic.demand_id)
                    ),
                    Demand.id.in_(
                        Partic.query.filter(
                            Partic.is_robot.is_(False),
                            Partic.is_training.is_(False),
                            Partic.status == ParticStatus.SUCCEED.value
                        ).with_entities(Partic.demand_id)
                    )
                )
    page = request.args.get('page', 1, type=int)
    pagination = demand_query.order_by(Demand.created_at.desc()).paginate(
        page, per_page=current_app.config['FLASKY_COMMENTS_PER_PAGE'],
        error_out=False)
    demands = pagination.items

    return render_template(
        'demand/demand_list.html',
        form=form,
        demands=demands,
        pagination=pagination,
        url_kwargs=url_kwargs,
        url='op_bp.demand_list',
        form_route='op_bp.demand_list',
        page_title='项目列表',
        can_export=True,
    )


@op_bp.route('/export/demand_list', methods=['GET', 'POST'])
@login_required
def export_demand_list():
    form = SearchDemandForm(formdata=request.form or request.args)
    template_path = '/tmp/demand_clients_{}.xls'.format(datetime.datetime.now())
    book = xlwt.Workbook(encoding='utf-8')
    fields = [
        '项目ID', '用户ID', '手机号', '用户昵称', '需求分类', '故障', '类型', '环境', '大小', '颜色', '间距', '省份',
        '城市', '预算', '成功人数', '标签', '来源', '状态', '客服', '发布次数', '创建时间', '发布时间'
    ]

    sheet = book.add_sheet('需求列表')

    for index, value in enumerate(fields):
        sheet.write(0, index, value)

    # write content
    demand_query = Demand.query.filter_by(deleted_at=0)
    if form.validate():

        # 如果根据发布者手机号查找，则根据 relationship 查找 Demand
        if form.user_phone.data:
            owner = User.query.filter_by(
                mobile=form.user_phone.data, deleted_at=0).first()
            if owner:
                demand_query = owner.demands
            else:
                demand_query = demand_query.filter_by(uid=0)

        query_kwargs, url_kwargs = form.generate_kwargs()

        # 根据 form 表单的值，构造Demand的query对象
        demand_query = Demand.generate_list_query_condition(
            args=query_kwargs,
            query=demand_query
        )

        if form.has_audio.data:
            demand_query = demand_query.filter(
                Demand.audio != ''
            )

        if form.is_for_gold.data == Choice.YES.value:
            demand_query = demand_query.filter(
                Demand.for_gold.is_(True)
            )
        elif form.is_for_gold.data == Choice.NO.value:
            demand_query = demand_query.filter(
                Demand.for_gold.is_(False)
            )

        if form.ignore_invalid.data:
            demand_query = demand_query.filter(
                Demand.status != DemandStatus.INVALID.value
            )

        if form.robot_win.data:
            robot_win_selected = form.robot_win.data
            if robot_win_selected == 1:
                demand_query = demand_query.filter(
                    Demand.id.in_(
                        Partic.query.filter(
                            Partic.is_robot.is_(True),
                            Partic.is_training.is_(False),
                            Partic.status == ParticStatus.SUCCEED.value
                        ).with_entities(Partic.demand_id)
                    )
                )
            elif robot_win_selected == 2:
                demand_query = demand_query.filter(
                    Demand.id.in_(
                        Partic.query.filter(
                            Partic.is_robot.is_(True),
                            Partic.is_training.is_(False),
                            Partic.status == ParticStatus.SUCCEED.value
                        ).with_entities(Partic.demand_id)
                    ),
                    Demand.id.notin_(
                        Partic.query.filter(
                            Partic.is_robot.is_(False),
                            Partic.is_training.is_(False),
                            Partic.status == ParticStatus.SUCCEED.value
                        ).with_entities(Partic.demand_id)
                    )
                )
            elif robot_win_selected == 3:
                demand_query = demand_query.filter(
                    Demand.id.in_(
                        Partic.query.filter(
                            Partic.is_robot.is_(True),
                            Partic.is_training.is_(False),
                            Partic.status == ParticStatus.SUCCEED.value
                        ).with_entities(Partic.demand_id)
                    ),
                    Demand.id.in_(
                        Partic.query.filter(
                            Partic.is_robot.is_(False),
                            Partic.is_training.is_(False),
                            Partic.status == ParticStatus.SUCCEED.value
                        ).with_entities(Partic.demand_id)
                    )
                )

    data = [
        (
            i.id,
            i.owner.id if i.owner else '',
            i.owner.mobile if i.owner and i.owner.is_engineer else encrypt(i.owner and i.owner.mobile or ''),
            i.owner.nickname if i.owner else '',
            i.cate_display,
            i.malf_display,
            i.type_display,
            i.surr_display,
            i.size,
            i.color_display,
            i.span,
            i.prov,
            i.city,
            i.budget,
            i.partic_success_cnt,
            i.tag_display,
            i.source,
            i.status_display,
            i.admin_display,
            i.published_count,
            i.created_datetime,
            i.published_datetime,
        ) for i in demand_query.order_by(Demand.created_at.desc()).all()
    ]
    for index, each in enumerate(data):
        for num, value in enumerate(each):
            sheet.write(index+1, num, value)
    book.save(template_path)
    if not os.path.exists(template_path):
        abort(404)
    return send_file(template_path, as_attachment=True)


@op_bp.route('/demand_list_for_sem', methods=['GET', 'POST'])
@login_required
def demand_list_for_sem():
    form = SearchDemandForSemForm(formdata=request.form or request.args)

    demand_query = Demand.query.filter_by(deleted_at=0, for_gold=False)
    url_kwargs = {}
    if form.validate():

        # 如果根据发布者手机号查找，则根据 relationship 查找 Demand
        if form.user_phone.data:
            owner = User.query.filter_by(
                mobile=form.user_phone.data, deleted_at=0).first()
            if owner:
                demand_query = owner.demands
            else:
                demand_query = demand_query.filter_by(uid=0)

        query_kwargs, url_kwargs = form.generate_kwargs()

        # 根据 form 表单的值，构造Demand的query对象
        demand_query = Demand.generate_list_query_condition(
            args=query_kwargs,
            query=demand_query
        )

    page = request.args.get('page', 1, type=int)
    pagination = demand_query.order_by(Demand.created_at.desc()).paginate(
        page, per_page=current_app.config['FLASKY_COMMENTS_PER_PAGE'],
        error_out=False)
    demands = pagination.items

    return render_template(
        'demand/demand_list_for_sem.html',
        form=form,
        demands=demands,
        pagination=pagination,
        url_kwargs=url_kwargs,
    )


@op_bp.route('/finish_img/review/demand_list', methods=['GET', 'POST'])
@login_required
def demand_finish_img_review_list():
    """
    created by chenhj at 2017/9/18
    结项图片待审核列表
    """
    form = SearchDemandForm(formdata=request.form or request.args)

    subquery = DemandFinishImg.query.filter(
        DemandFinishImg.state == DemandFinishImgState.UNCHECKED.value
    ).with_entities(
        DemandFinishImg.demand_id.distinct()
    ).subquery()

    demand_query = Demand.query.filter(
        Demand.id.in_(subquery), Demand.deleted_at == 0
    )
    url_kwargs = {}
    if form.validate():

        # 如果根据发布者手机号查找，则根据 relationship 查找 Demand
        if form.user_phone.data:
            owner = User.query.filter_by(
                mobile=form.user_phone.data, deleted_at=0).first()
            if owner:
                demand_query = owner.demands
            else:
                demand_query = demand_query.filter_by(uid=0)

        query_kwargs, url_kwargs = form.generate_kwargs()

        # 根据 form 表单的值，构造Demand的query对象
        demand_query = Demand.generate_list_query_condition(
            args=query_kwargs,
            query=demand_query
        )

    page = request.args.get('page', 1, type=int)
    pagination = demand_query.order_by(Demand.created_at.desc()).paginate(
        page, per_page=current_app.config['FLASKY_COMMENTS_PER_PAGE'],
        error_out=False)
    demands = pagination.items

    return render_template(
        'demand/demand_list.html',
        form=form,
        demands=demands,
        pagination=pagination,
        url_kwargs=url_kwargs,
        url='op_bp.demand_finish_img_review_list',
        form_route='op_bp.demand_finish_img_review_list',
        page_title='结项待审核列表',
        can_export=False,
    )


@op_bp.route('/pending/demand_list', methods=['GET', 'POST'])
@login_required
def pending_demand_list():
    form = SearchDemandForm(formdata=request.form or request.args)

    subquery = Partic.query.filter(
        Partic.feedback_status == FeedbackStatus.PENDING.value
    ).with_entities(
        Partic.demand_id.distinct()
    ).subquery()

    demand_query = Demand.query.filter(
        Demand.id.in_(subquery), Demand.deleted_at == 0
    )
    url_kwargs = {}
    if form.validate():

        # 如果根据发布者手机号查找，则根据 relationship 查找 Demand
        if form.user_phone.data:
            owner = User.query.filter_by(
                mobile=form.user_phone.data, deleted_at=0).first()
            if owner:
                demand_query = owner.demands
            else:
                demand_query = demand_query.filter_by(uid=0)

        query_kwargs, url_kwargs = form.generate_kwargs()

        # 根据 form 表单的值，构造Demand的query对象
        demand_query = Demand.generate_list_query_condition(
            args=query_kwargs,
            query=demand_query
        )

        if form.has_audio.data:
            demand_query = demand_query.filter(
                Demand.audio != ''
            )

        if form.is_for_gold.data == Choice.YES.value:
            demand_query = demand_query.filter(
                Demand.for_gold.is_(True)
            )
        elif form.is_for_gold.data == Choice.NO.value:
            demand_query = demand_query.filter(
                Demand.for_gold.is_(False)
            )

    page = request.args.get('page', 1, type=int)
    pagination = demand_query.order_by(Demand.created_at.desc()).paginate(
        page, per_page=current_app.config['FLASKY_COMMENTS_PER_PAGE'],
        error_out=False)
    demands = pagination.items

    return render_template(
        'demand/pending_demand_list.html',
        form=form,
        demands=demands,
        pagination=pagination,
        url_kwargs=url_kwargs,
        url='op_bp.pending_demand_list',
        form_route='op_bp.pending_demand_list',
        page_title='反馈审核管理',
        can_export=True,
    )


@op_bp.route('/export/pending/demand_list', methods=['GET', 'POST'])
@login_required
def export_pending_demand_list():
    form = SearchDemandForm(formdata=request.form or request.args)
    template_path = '/tmp/pending_demand_clients_{}.xls'.format(datetime.datetime.now())
    book = xlwt.Workbook(encoding='utf-8')
    fields = [
        '用户ID', '手机号', '用户昵称', '需求分类', '故障', '类型', '环境', '大小', '颜色', '间距', '省份', '城市',
        '预算', '成功人数', '标签', '来源', '状态', '客>服', '发布次数', '创建时间', '发布时间'
    ]

    sheet = book.add_sheet('需求列表')

    for index, value in enumerate(fields):
        sheet.write(0, index, value)

    # write content
    subquery = Partic.query.filter(
        Partic.feedback_status == FeedbackStatus.PENDING.value
    ).with_entities(
        Partic.demand_id.distinct()
    ).subquery()

    demand_query = Demand.query.filter(
        Demand.id.in_(subquery), Demand.deleted_at == 0
    )
    if form.validate():
        # 如果根据发布者手机号查找，则根据 relationship 查找 Demand
        if form.user_phone.data:
            owner = User.query.filter_by(
                mobile=form.user_phone.data, deleted_at=0).first()
            if owner:
                demand_query = owner.demands
            else:
                demand_query = demand_query.filter_by(uid=0)

        query_kwargs, url_kwargs = form.generate_kwargs()

        # 根据 form 表单的值，构造Demand的query对象
        demand_query = Demand.generate_list_query_condition(
            args=query_kwargs,
            query=demand_query
        )

        if form.has_audio.data:
            demand_query = demand_query.filter(
                Demand.audio != ''
            )

        if form.is_for_gold.data == Choice.YES.value:
            demand_query = demand_query.filter(
                Demand.for_gold.is_(True)
            )
        elif form.is_for_gold.data == Choice.NO.value:
            demand_query = demand_query.filter(
                Demand.for_gold.is_(False)
            )

    data = [
        (
            i.owner.id if i.owner else '',
            i.owner.mobile if i.owner and i.owner.is_engineer else encrypt(i.owner and i.owner.mobile or ''),
            i.owner.nickname if i.owner else '',
            i.cate_display,
            i.malf_display,
            i.type_display,
            i.surr_display,
            i.size,
            i.color_display,
            i.span,
            i.prov,
            i.city,
            i.budget,
            i.partic_success_cnt,
            i.tag_display,
            i.source,
            i.status_display,
            i.admin_display,
            i.published_count,
            i.created_datetime,
            i.published_datetime,
        ) for i in demand_query.order_by(Demand.created_at.desc()).all()
    ]
    for index, each in enumerate(data):
        for num, value in enumerate(each):
            sheet.write(index+1, num, value)
    book.save(template_path)
    if not os.path.exists(template_path):
        abort(404)
    return send_file(template_path, as_attachment=True)


@op_bp.route('/add_demand', methods=['GET', 'POST'])
@login_required
def add_demand():
    form = CreateDemandForm()
    form.audio_id.choices = NotChoices
    if form.validate_on_submit():
        mobile = form.user_mobile.data.strip()

        user = User.query.filter_by(
            mobile=mobile,
            deleted_at=0
        ).first()
        if not user:
            user = User(
                mobile=mobile,
                nickname=form.user_nickname.data,
                type=UserType.CLIENT.value,
                prov=form.prov.data,
                city=form.city.data,
            )
            db.session.add(user)
            db.session.flush()
            # 新建用户后，如果有该用户的录音，同步更新Audio记录的uid
            audios = Audio.query.filter_by(mobile=user.mobile)
            for audio in audios:
                audio.uid = user.id

        # fixed by zhouhl
        # 新建项目时，将相关用户的录音放入 form.audio_id.choices 中
        form.audio_id.choices = NotChoices + \
            [(i.id, 'http://audio.wanpinghui.com/' + i.audio) for i in user.audios]

        demand = Demand()
        form.populate_obj(demand)

        # 项目来源为-1时，修改为空字符串
        if str(demand.source) == "-1":
            demand.source = ''

        # 如果是安装项目，则故障默认为其他
        if demand.cate == DemandCate.INSTALL.value:
            demand.malf = DemandMalf.OTHER_PROBLEM.value
        # 处理用户ID
        demand.uid = user.id
        # 处理区域ID
        area = Area.query.filter_by(name=form.city.data, type=AreaType.CITY.value).first()
        if area:
            demand.area_id = area.id
        # 处理录音路径
        if form.audio_id.data:
            tmp_audio = Audio.query.filter_by(id=form.audio_id.data).first()
            if tmp_audio:
                demand.audio = tmp_audio.audio

        # TODO 增加虚拟对话token

        db.session.add(demand)
        db.session.flush()

        flash('新增项目成功')
        return redirect(url_for('op_bp.demand_info', demand_id=demand.id))
    else:
        print(form.errors)

    return render_template(
        "demand/demand_add.html",
        form=form,
        demand_type_dic=DemandType.to_dict(),
        demand_rent_type_dic=DemandRentType.to_dict(),
    )


@op_bp.route('/demand/<int:demand_id>', methods=['GET'])
@login_required
def demand_info(demand_id):
    demand = Demand.query.get_or_404(demand_id)
    form = DetailDemandForm(obj=demand)
    if demand.publish_remark:
        form.publish_remark.data = json.dumps(
            json.loads(demand.publish_remark), ensure_ascii=False, indent=2
        )

    # alter by zhouhl 2017-09-28 圆满完成/无效的项目状态不可修改
    if demand.status in (
        DemandStatus.DONE.value, DemandStatus.INVALID.value
    ):
        read_only(form.status)

    # 项目状态的可选项
    # 抢单结束 ==> 圆满完成/无效
    # 草稿/待审核 ==> 草稿/待审核/无效
    # 铜牌一口价/进行中/二次竞价中/竞价结束/免费竞价中 ==> 抢单完成/圆满完成/无效
    # 其他 ==> 当前状态/无效
    if demand.status in (
        DemandStatus.COMPETE_DONE.value,
    ):
        form.status.choices = [
            DemandStatus.COMPETE_DONE.args,
            DemandStatus.DONE.args,
            DemandStatus.INVALID.args,
        ]
    elif demand.status in (
        DemandStatus.DRAFT.value,
        DemandStatus.UNCHECKED.value
    ):
        form.status.choices = [
            DemandStatus.DRAFT.args,
            DemandStatus.UNCHECKED.args,
            DemandStatus.INVALID.args,
        ]
    elif demand.status in (
        DemandStatus.BRONZE_MEDAL_DISCOUNT.value,
        DemandStatus.DISPATCHING.value,
        DemandStatus.MASTER_DISPATCHING.value,
        DemandStatus.ONGOING.value,
        DemandStatus.BID_DONE.value,
        DemandStatus.SECOND_ONGOING.value,
        DemandStatus.FREE_ONGOING.value
    ):
        form.status.choices = [
            DemandStatus(demand.status).args,
            DemandStatus.COMPETE_DONE.args,
            DemandStatus.DONE.args,
            DemandStatus.INVALID.args,
        ]
    else:
        form.status.choices = [
            DemandStatus(demand.status).args,
            DemandStatus.INVALID.args,
        ]

    # 历史备注信息
    form.history_admin_remark.data = form.admin_remark.data
    form.admin_remark.data = ""

    form.user_nickname.data = demand.owner.nickname
    form.user_mobile.data = demand.owner.mobile

    form.edit_created_at.data = datetime.datetime.fromtimestamp(
        demand.created_at)
    if demand.published_at:
        form.edit_published_at.data = datetime.datetime.fromtimestamp(
            demand.published_at)
    # NOTE 因为city与prov联动，所以预先将city放入choices中
    form.city.choices = [(form.city.data, form.city.data)]

    # 可关联的录音文件，根据手机号查找
    # TODO 录音文件的具体地址需要同步修改
    audios = list(demand.owner.audios)
    form.audio_id.choices = NotChoices + \
        [(i.id, 'http://audio.wanpinghui.com/' + i.audio) for i in audios]

    # 可关联的项目完成图片
    finish_imgs = list(demand.finish_imgs)
    demand_finish_img_flag = False
    demand_finish_img = None
    for i in finish_imgs:
        if i.state == DemandFinishImgState.CHECKED.value:
            demand_finish_img_flag = True
            demand_finish_img = i
            break

    if not demand_finish_img_flag:
        form.finish_img_id.choices = NotChoices + \
            [(i.id, i.img_url) for i in finish_imgs]
        finish_img_map = {str(i.id): i for i in finish_imgs}
    else:
        form.finish_img_id.choices = []
        finish_img_map = {}

    # 处理audio的默认值
    if demand.audio:
        for i in audios:
            # 兼容之前错误地将audio.id存入demand.audio
            if i.audio == demand.audio or str(i.id) == demand.audio:
                form.audio_id.data = i.id
                break

    audio_date_map = {str(i.id): i.record_datetime or i.record_date for i in audios}

    # 项目的相关抢单记录
    partics = []
    published_count = demand.published_count
    for i in range(1, published_count+1):
        partics.append(list(Partic.demand_partics(
            demand, i, True, ignore_training=False
        )))

    # 客服跟进信息
    # comments = list(demand.comments)

    # alter by zhouhl 2018-03-05 历史通话记录从seven_moor的查询
    his_outbound_records = SevenMoorRecord.query.filter(
        or_(
            SevenMoorRecord.call_no == demand.owner.mobile,
            SevenMoorRecord.called_no == demand.owner.mobile
        )
    ).order_by(
        SevenMoorRecord.id.desc()
    ).all()

    # 计划外呼
    schedule_outbound_records = demand.owner.outbound_records.filter_by(
        queue_state=QueueState.WAITING.value
    ).order_by(
        OutBoundRecord.id.desc()
    ).all()

    # 跟进信息
    follow_list = Partic.query.filter(
        Partic.demand_id == demand_id,
        Partic.status == ParticStatus.SUCCEED.value,
        Partic.deleted_at == 0,
    ).order_by(
        Partic.bid_win_at.desc()
    )

    return render_template(
        "demand/demand_info.html",
        demand=demand,
        form=form,
        audios=audios,
        audio_date_map=audio_date_map,
        partics=partics,
        # comments=comments,
        demand_type_dic=DemandType.to_dict(),
        demand_rent_type_dic=DemandRentType.to_dict(),
        his_outbound_records=his_outbound_records,
        schedule_outbound_records=schedule_outbound_records,
        finish_img_map=finish_img_map,
        demand_finish_img=demand_finish_img,
        follow_list=follow_list
    )


@op_bp.route('/edit_demand/<int:demand_id>', methods=['POST'])
@login_required
def edit_demand(demand_id):
    """
    编辑项目及其他后续处理

    alter by zhouhl 2017-11-30 拆分项目的编辑功能与发布功能
    原 PART 1，PART 4，PART 5 移动至 publish_demand

    alter by zhouhl 2017-09-28 整理此views代码，使其按照功能组织
    PART 1：检查是否有异常需要重定向并提示用户
    PART 2：根据表单，同步数据库数据
    PART 3：设置项目完成图片，给用户加积分
    PART 4：通知客户项目发布成功
    PART 5：通知工程商有新项目
    """

    demand = Demand.query.get_or_404(demand_id)
    form = DetailDemandForm()

    # alter by zhouhl 2017-09-28 圆满完成/无效的项目需要传递demand.status进form
    # 因为此时form.status被设置为read_only,取得的值为None
    if demand.status == DemandStatus.DONE.value:
        form.status.data = DemandStatus.DONE.value
    if demand.status == DemandStatus.INVALID.value:
        form.status.data = DemandStatus.INVALID.value

    # 将可关联的录音作为audio的可选项
    audios = list(demand.owner.audios)
    form.audio_id.choices = NotChoices + \
        [(i.id, 'http://audio.wanpinghui.com/' + i.audio) for i in audios]

    # 可关联的项目完成图片
    finish_imgs = list(demand.finish_imgs)
    form.finish_img_id.choices = NotChoices + \
        [(i.id, i.img_url) for i in finish_imgs]

    if form.validate_on_submit():
        # 若非草稿/待审核的订单, 不允许再修改成草稿/待审核
        if demand.status not in (
            DemandStatus.DRAFT.value, DemandStatus.UNCHECKED.value
        ) and form.status.data in (
            DemandStatus.DRAFT.value, DemandStatus.UNCHECKED.value
        ):
            flash('不允许将此项目置为[{}]状态'.format(DemandStatus(form.status.data).display))
            return redirect(url_for('op_bp.demand_info', demand_id=demand_id))

        # PART 2： 根据表单，同步数据库数据

        # alter by zhouhl 2017-12-25
        # 修改项目状态，不影响虚拟号关系。改由定时任务解绑

        # 历史备注，供后续比较状态改变使用
        history_admin_remark = demand.admin_remark

        # 如果是由草稿/待审核订单->无效订单, 则同时关闭demand_client的相关数据
        if demand.status in (
            DemandStatus.DRAFT.value, DemandStatus.UNCHECKED.value
        ) and form.status.data == DemandStatus.INVALID.value:
            DemandClient.complete(
                demand.uid, current_user.id,
                OperateResult.INVALID_DEMAND.value, demand.id
            )

        form.populate_obj(demand)

        # 需要同步更新demand.area_id
        area = Area.query.filter_by(
            name=form.city.data,
            type=AreaType.CITY.value
        ).first()
        if area:
            demand.area_id = area.id

        # 项目来源为"-1"时，修改为空字符串
        if str(demand.source) == "-1":
            demand.source = ''

        demand.owner.nickname = form.user_nickname.data

        # 处理 edit_created_at ，更新 created_at
        # PS：因为 MyDateTimeField 做了转换，所以直接更新
        if form.edit_created_at.data:
            demand.created_at = form.edit_created_at.data
        if form.edit_published_at.data:
            demand.published_at = form.edit_published_at.data

        # 处理内部备注
        if form.admin_remark.data:
            append_admin_remark = '{admin_remark} | {admin} | {date}'.format(
                admin_remark=form.admin_remark.data,
                admin=current_user.nickname or current_user.username,
                date=datetime.datetime.today().strftime('%Y-%m-%d %H:%M:%S')
            )
            demand.admin_remark = append_admin_remark + '\n' \
                + history_admin_remark
        else:
            demand.admin_remark = history_admin_remark

        # FIXME 数据库里设置了audio为非空的坑
        if not form.audio_id.data:
            demand.audio = ''
        else:
            tmp_audio = Audio.query.filter_by(id=form.audio_id.data).first()
            if tmp_audio:
                demand.audio = tmp_audio.audio
            else:
                demand.audio = ''

        # PART 3：设置项目完成图片，给用户加积分
        if form.finish_img_id.data:
            tmp_finish_img = DemandFinishImg.query.filter_by(
                id=form.finish_img_id.data).first()
            if tmp_finish_img:
                for i in demand.finish_imgs:
                    i.state = DemandFinishImgState.INVALID.value
                tmp_finish_img.state = DemandFinishImgState.CHECKED.value

                # 项目状态直接修改为DemandStatus.DONE
                demand.status = DemandStatus.DONE.value
                # alter by zhouhl 2017-12-25 项目为圆满完成时，不解绑虚拟号
                # 改由定时任务，在最近使用30天后解绑

                # 根据项目类型增加对应积分
                inc_credit_map = {
                    DemandCate.INSTALL.value:
                        UserCreditRule.FINISH_DEMAND_INSTALL.value,
                    DemandCate.MAINTAIN.value:
                        UserCreditRule.FINISH_DEMAND_MAINTAIN.value,
                    DemandCate.RENT.value:
                        UserCreditRule.FINISH_DEMAND_RENT.value,
                }
                inc_credit = inc_credit_map.get(demand.cate, 0)
                tmp_finish_img.owner.credit += inc_credit

                # 发送微信模板消息，通知工程商
                to_send_data = []
                host = os.environ.get('wph_host') or request.host_url
                for i in demand.finish_imgs:
                    if not i.owner.wx_openid:
                        continue
                    if i.state == DemandFinishImgState.CHECKED.value:
                        # 发送审核通过消息
                        code = 'FINISH_IMG_CHECKED'
                        template_id, data, url = WX_TEMPLATE_FUNC_MAP[code](
                            demand, host, inc_credit)
                    else:
                        # 发送审核不通过消息
                        code = 'FINISH_IMG_INVALID'
                        template_id, data, url = WX_TEMPLATE_FUNC_MAP[code](
                            demand, host)

                    to_send_data.append({
                        'openid': i.owner.wx_openid,
                        'template_id': template_id,
                        'data': data,
                        'url': url
                    })
                    db.session.add(WechatSendLog(
                        openid=i.owner.wx_openid,
                        code=code,
                        content=json.dumps(data, ensure_ascii=False),
                        url=url,
                        user_id=i.owner.id,
                        user_nickname=i.owner.nickname,
                        user_mobile=i.owner.mobile
                    ))
                # 每100条发送一次
                queue_send_count = 100
                for i in range(0, len(to_send_data), queue_send_count):
                    current_app.wechat_producer.send(
                        json.dumps(to_send_data[i:i+queue_send_count]))

        flash('编辑项目成功。')
        return redirect(url_for('op_bp.demand_list', id=demand.id))

    else:
        flash('编辑出错，请检查表单数据(如省份, 城市等数据)。')
        return redirect(url_for('op_bp.demand_info', demand_id=demand.id))


@op_bp.route('/publish_demand/<int:demand_id>', methods=['GET', 'POST'])
@login_required
def publish_demand(demand_id):
    """
    add by zhouhl 2017-11-30
    发布项目
    PART 1：检查是否有异常需要重定向并提示用户
    PART 2: 修改项目状态，并启动定时任务
    PART 3：通知客户项目发布成功
    PART 4：通知工程商有新项目
    PART 5: 如果是延后发布的项目，同时将DemandClient的operate_result更新为DELAY_PUBLISH_DONE
    PART 6: 记录该次发布操作相关信息
    """
    demand = Demand.query.get_or_404(demand_id)
    form = PublishDemandForm(obj=demand)

    if demand.published_count > 1:
        flash('发布过2次的订单不允许再次发布')
        return redirect(url_for('op_bp.demand_list'))

    if demand.published_count == 1 and demand_winner_count(demand_id, db.session) >= BID_USER_COUNT:
        flash('已有3个抢单成功工程商, 不允许再次发布')
        return redirect(url_for('op_bp.demand_list'))

    if request.method == 'GET':
        form.user_nickname.data = demand.owner.nickname
        form.user_mobile.data = demand.owner.mobile
        form.current_demand_status.data = demand.status_display
        if demand.published_count > 1:
            form.notice_pay.data = False

        if demand.status in (DemandStatus.DRAFT.value, DemandStatus.UNCHECKED.value):
            form.dispatch_count.data = 3
            form.demand_status.choices = [
                DemandStatus.MASTER_DISPATCHING.args,
                DemandStatus.DISPATCHING.args,
                DemandStatus.BRONZE_MEDAL_DISCOUNT.args,
                DemandStatus.ONGOING.args,
            ]

        if demand.status == DemandStatus.DELAY.value:
            form.demand_status.choices = [
                DemandStatus.MASTER_DISPATCHING.args,
            ]

        # 二次发布只允许从"优先抢单"开始
        if demand.published_count > 0:

            form.demand_status.choices = [
                (0, '--请选择项目状态--'),
                DemandStatus.BRONZE_MEDAL_DISCOUNT.args,
                DemandStatus.ONGOING.args,
                DemandStatus.SECOND_ONGOING.args,
                DemandStatus.FREE_ONGOING.args,
            ]

        # 维修只进入一口价
        if demand.cate == DemandCate.MAINTAIN.value:
            form.demand_status.choices = [
                DemandStatus.SECOND_ONGOING.args
            ]

        # 计划外呼
        schedule_outbound_records = demand.owner.outbound_records.filter_by(
            queue_state=QueueState.WAITING.value
        ).order_by(
            OutBoundRecord.id.desc()
        ).all()
        return render_template(
            'demand/demand_publish.html',
            form=form,
            demand=demand,
            schedule_outbound_records=schedule_outbound_records,
        )

    if form.validate_on_submit():
        # PART 1： 检查是否有异常需要重定向并提示用户

        if form.notice_way.data != DemandNoticeWay.NO_NOTICE.value \
                and not current_app.config['RABBITMQ']:
            flash('RABBITMQ服务未启动, 请联系管理员。', category='error')
            return redirect(url_for('op_bp.publish_demand', demand_id=demand.id))

        # 修改客户姓名
        demand.owner.nickname = form.user_nickname.data

        # PART 1.1: 预检查, 如果用户昵称为纯数字, 则不允许发布
        if demand.owner.nickname.isdigit():
            flash('用户昵称为手机, 不允许发布')
            return redirect(url_for('op_bp.publish_demand', demand_id=demand.id))

        # PART 2: 修改项目状态，并启动定时任务

        # 若是仅通知, 则不跑以下逻辑
        if not form.only_notify.data:
            if demand.status in (
                DemandStatus.ONGOING.value,
                DemandStatus.DISPATCHING.value,
                DemandStatus.MASTER_DISPATCHING.value,
                DemandStatus.BRONZE_MEDAL_DISCOUNT.value
            ):
                flash('发布项目出错: 派单中/铜牌一口价中/首次竞价中的项目不允许做二次发布操作')
                return redirect(url_for('op_bp.publish_demand', demand_id=demand.id))

            if not form.demand_status.data:
                flash('发布项目出错: 请选择新项目状态')
                return redirect(url_for('op_bp.publish_demand', demand_id=demand.id))
            if demand.is_bid_status:
                flash('发布项目出错: 当前项目正在中标流程，不允许进行二次发布')
                return redirect(
                    url_for('op_bp.publish_demand', demand_id=demand.id)
                )

            if demand.is_locked:
                flash('发布项目出错: 当前项目已被独家锁定，不允许进行二次发布')
                return redirect(
                    url_for('op_bp.publish_demand', demand_id=demand.id)
                )
            if demand.status in (DemandStatus.DONE.value, DemandStatus.INVALID.value):
                flash('发布项目出错: 当前项目已圆满完成，不允许进行二次发布')
                return redirect(
                    url_for('op_bp.publish_demand', demand_id=demand.id)
                )
            if Partic.query.filter(
                Partic.demand_id == demand.id,
                Partic.status == ParticStatus.SUCCEED.value,
                Partic.follow_type == FollowType.NA.value
            ).first():
                flash('发布项目出错: 仍有中标人未跟进, 不允许做二次发布')
                return redirect(
                    url_for('op_bp.publish_demand', demand_id=demand.id)
                )
            old_demand_status = demand.status
            demand.publish_pre_deal()
            # 发布日志
            publish_remark = json.loads(demand.publish_remark or '{}')
            if form.current_publish_remark.data:
                publish_remark[demand.published_count] = form.current_publish_remark.data
                demand.publish_remark = json.dumps(publish_remark)
            # add by zhouhl 2018-03-06 如果发布"延时发布(未发布)"的项目，则更新为"延时发布(已发布)"
            if old_demand_status == DemandStatus.DELAY.value:
                demand.handle_complete(
                    current_user.id,
                    OperateResult.DELAY_PUBLISH_DONE.value,
                    demand.owner.id
                )
            else:
                DemandClient.complete(demand.uid, current_user.id, demand.cate, demand.id)

            # 增加外呼任务
            if form.next_outbound.data:
                tmp_outbound_record, err = create_outbound_mission(
                    mobile=demand.owner.mobile,
                    call_reason=form.next_outbound_call_reason.data,
                    schedule_time=form.next_outbound_schedule_time.data,
                    try_again=True,
                    user_id=demand.owner.id
                )
                if tmp_outbound_record:
                    db.session.add(tmp_outbound_record)

        db.session.commit()
        # PART 4：通知工程商有新项目

        # 根据 notice_way 选择通知的客户范围
        # 根据项目所在城市，查询关注该地区的客户，同时区分已绑定客户和未绑定客户
        # 包含字段 pack_id,pack_sms_point,user_id,user_nickname,user_mobile,user_wx_openid

        proxy = MsgProxy()

        # 首次发布通知拉新促活用户
        # 暂停拉新短信 2018-09-04 （小罗）
        # if demand.published_count == 1:
        #     proxy.marketing_producer.potential_demand_message(demand_id)

        # 已绑定用户,微信通知
        if form.notice_way.data in (
            DemandNoticeWay.JUST_WX.value, DemandNoticeWay.FIRST_WX.value
        ):
            # alter by zhouhl 2017-11-02
            # 区分忠实用户和营销用户
            # 营销用户又分为有短信点和没短信点（sms_point）
            # 有短信点的营销用户推送行为与忠实用户一致
            # 没有短信点的营销用户每天最多推送一条安装项目
            # 派单走单独派单流程

            direct_notify = (
                form.demand_status.data == DemandStatus.ONGOING.value or
                demand.cate == DemandCate.MAINTAIN.value
            )
            # app 通知
            proxy.im_msg_producer.new_notify(
                demand_id,
                form.demand_status.data,
                direct_notify=direct_notify,
                admin_user_id=current_user.id
            )

        # 清除缓存partic信息
        Partic.clear_demand_partic_cache_data(current_app.redis, demand.id)

        flash('发布项目成功，请勿重复发布！')
        return redirect(url_for('op_bp.demand_list', id=demand.id))
    else:
        print(form.errors)
        flash('发布项目出错，请检查表单数据')
        return redirect(url_for('op_bp.publish_demand', demand_id=demand.id))


@op_bp.route('/ajax/approval/feedback', methods=['PUT'])
@login_required
def ajax_approval_feedback():
    """
    create by ld_wu 2017-09-12
    反馈信息审核
    """

    data = request.get_json()
    partic_id = data.get('partic_id')
    feedback_status = data.get('feedback_status')
    minus_credit = int(data.get('minus_credit', 0))
    feedback_refuse_content = data.get('feedback_refuse_content', '')

    if feedback_status not in (
            FeedbackStatus.PASS.value, FeedbackStatus.REFUSE.value):
        return jsonify(feedback_status=0)

    partic = Partic.query.filter(
        Partic.id == partic_id,
        Partic.status.in_(
            [ParticStatus.SUCCEED.value, ParticStatus.TIMEOUT_NOT_CONTACT.value]
        )
    ).first()

    status = 0
    if partic:
        if (partic.feedback_status == FeedbackStatus.PENDING.value and
                partic.feedback):
            # 返点
            if not partic.is_revert and feedback_status == FeedbackStatus.PASS.value:
                partic.feedback_pass()
            elif feedback_status == FeedbackStatus.REFUSE.value:
                # 驳回时的微信通知
                partic.feedback_refuse(minus_credit, feedback_refuse_content)
        status = partic.feedback_status

    return jsonify(
        feedback_status=FeedbackStatus(status).display
    )


@op_bp.route('/bid/suppliers', methods=['GET', 'POST'])
@login_required
def bid_suppliers():
    """
    create by ld_wu 2017-11-30
    最近中标的工程商列表
    :return:
    """
    form = SearchBidSupplierForm(request.form or request.args)

    url_kwargs = dict()

    page = request.args.get('page', 1, type=int)

    # FIXME by ld_wu 2017-11-30 需要把已回访的过滤掉
    query = Partic.query.filter(
        Partic.status == ParticStatus.SUCCEED.value,
        Partic.bid_win_at != 0,
        Partic.contact_state == ContactState.YES.value
    ).order_by(
        Partic.bid_win_at.desc()
    ).join(
        Demand,
        Demand.id == Partic.demand_id
    )

    if form.validate():

        query_kwargs, url_kwargs = form.generate_kwargs()

        query = Partic.generate_list_query_condition(
            args=query_kwargs,
            query=query
        )

    pagination = query.paginate(
        page,
        per_page=current_app.config['FLASKY_COMMENTS_PER_PAGE'],
        error_out=False
    )
    partics = pagination.items

    return render_template(
        'demand/bid_supplier_list.html',
        form=form,
        partics=partics,
        pagination=pagination,
        url_kwargs=url_kwargs,
    )


@op_bp.route('/win_bid_follow', methods=['POST'])
@login_required
def win_bid_follow():
    data = request.form
    partic_id = data['partic_id']
    follow_type = data['follow_type']
    follow_msg = data['follow_msg']
    minus_credit = data['minus_credit']

    partic = Partic.get_by_args(
        id=partic_id, status=ParticStatus.SUCCEED.value
    )

    # 独家锁定, 放弃跟进不允许继续跟进
    if partic.follow_type in (FollowType.LOCKED.value, FollowType.GIVE_UP.value):
        flash('该跟进状态为{}, 不允许做跟进操作'.format(partic.follow_type_display))
        return redirect(url_for('op_bp.demand_info', demand_id=partic.demand_id))

    # 如果要把某个跟进状态变成独家锁定, 则需要先判断是否有多个跟进人
    if int(follow_type) == FollowType.LOCKED.value and Partic.query.filter(
        Partic.demand_id == partic.demand_id,
        Partic.status == ParticStatus.SUCCEED.value,
        Partic.follow_type.in_([FollowType.KEEP.value, FollowType.NA.value]),
    ).count() > 1:
        flash('该项目有多个继续跟进(或未跟进)人, 不允许独家锁定')
        return redirect(url_for('op_bp.demand_info', demand_id=partic.demand_id))

    demand = Demand.get_by_id(partic.demand_id)

    # 该项目已被独家锁定, 不允许再做跟进操作
    if demand.is_locked:
        flash('该项目已被独家锁定, 不允许再做跟进操作')
        return redirect(url_for('op_bp.demand_info', demand_id=partic.demand_id))

    partic.follow_type = follow_type
    partic.follow_msg = follow_msg
    partic.feedback_status = FeedbackStatus.PASS.value

    # 若为放弃跟进, 则解绑虚拟号关系, 并扣成就值
    if int(partic.follow_type) == FollowType.GIVE_UP.value:
        from app.axb.models import VirtualNumber
        VirtualNumber.unbind_virtual_by_partic(partic)
        supplier = User.get_by_id(partic.supplier_id)
        supplier.credit -= int(minus_credit)

    # 独家锁定, 则加成就值
    if int(partic.follow_type) == FollowType.LOCKED.value:
        supplier = User.get_by_id(partic.supplier_id)
        supplier.credit += UserCreditRule.DEMAND_LOCKED.value

    flash('跟进成功')
    return redirect(url_for('op_bp.demand_info', demand_id=partic.demand_id))


@op_bp.route('/demand_clients', methods=['GET', 'POST'])
@login_required
def demand_clients():
    """
    created by chenhj at 15/01/2018

    """
    form = SearchDemandClientForm(formdata=request.form or request.args)

    demand_client_query = DemandClient.query.outerjoin(
        Demand, Demand.id == DemandClient.demand_id
    ).filter(
        DemandClient.deleted_at == 0
    )
    url_kwargs = {}
    if form.validate():

        # 如果根据客户手机号查找
        if form.user_mobile.data:
            owner = User.query.filter_by(
                mobile=form.user_mobile.data, deleted_at=0).first()
            if owner:
                demand_client_query = demand_client_query.filter(
                    DemandClient.user_id == owner.id
                )
            else:
                demand_client_query = demand_client_query.filter(
                    DemandClient.user_id == 0
                )
            url_kwargs['user_mobile'] = form.user_mobile.data

        # 如果根据客户昵称查找(手机号优先级比昵称高)
        if form.user_nickname.data and not form.user_mobile.data:
            owner = User.query.filter_by(
                nickname=form.user_nickname.data, deleted_at=0).all()
            if owner:
                user_ids = [i.id for i in owner]
                demand_client_query = demand_client_query.filter(
                    DemandClient.user_id.in_(user_ids)
                )
            url_kwargs['user_nickname'] = form.user_nickname.data

        if int(form.operate_time_filter.data) > 0:
            dic = {
                '1': (0, 300),
                '2': (300, 600),
                '3': (600, 1200),
                '4': (1200, 1800),
                '5': (1800, 3600),
                '6': (3600,)
            }
            op_time_data = dic[form.operate_time_filter.data]
            demand_client_query = demand_client_query.filter(
                DemandClient.operate_time >= op_time_data[0]
            )
            if len(op_time_data) > 1:
                demand_client_query = demand_client_query.filter(
                    DemandClient.operate_time <= op_time_data[1]
                )
            url_kwargs['operate_time_filter'] = form.operate_time_filter.data

        if form.last_visit_time_begin.data:
            demand_client_query = demand_client_query.filter(
                DemandClient.last_visit_time >= form.last_visit_time_begin.data
            )

        if form.last_visit_time_end.data:
            demand_client_query = demand_client_query.filter(
                DemandClient.last_visit_time <= form.last_visit_time_end.data
            )

        if form.sem_path.data:
            demand_client_query = demand_client_query.filter(
                DemandClient.sem_path.like('%{}%'.format(form.sem_path.data))
            )

        # 表单其他参数处理
        for key, value in form.data.items():
            tmp_form_field = getattr(form, key)
            # 排除提交按钮
            # 排除未设置的form字段，value可能为0
            # 排除为值为 NotChoices 的 NonValidatingSelectField
            ignore_keys = (
                'submit','user_mobile', 'user_nickname', 'operate_time_filter',
                'sem_path', 'last_visit_time_begin', 'last_visit_time_end'
            )
            if key in ignore_keys:
                continue
            if value is None or value == '' or value is False:
                continue
            if isinstance(tmp_form_field, NonValidatingSelectField) \
                    and str(value) == str(NotChoices[0][0]):
                continue

            if isinstance(tmp_form_field, MyDateTimeField):
                url_kwargs[key] = tmp_form_field.raw_data[0]
            else:
                url_kwargs[key] = value

            # 根据key的分别对 DemandClient 或 Demand 的col增加查询条件
            if key in ('sem_plan', 'sem_unit', 'sem_keyword'):
                demand_client_query = demand_client_query.filter(
                    getattr(Demand, key) == value
                )
            else:
                demand_client_query = demand_client_query.filter(
                    getattr(DemandClient, key) == value
                )

    page = request.args.get('page', 1, type=int)
    pagination = demand_client_query.order_by(
        DemandClient.last_visit_time.desc()
    ).paginate(
        page, per_page=current_app.config['FLASKY_COMMENTS_PER_PAGE'],
        error_out=False)
    clients = pagination.items

    return render_template(
        'demand/demand_client_list.html',
        form=form,
        clients=clients,
        pagination=pagination,
        url_kwargs=url_kwargs,
    )


@op_bp.route('/export/demand_clients')
@login_required
def export_demand_clients():
    """
    导出demand_client查询结果
    add by zhouhl 2018-03-27
    """
    form = SearchDemandClientForm(formdata=request.form or request.args)
    template_path = '/tmp/demand_clients_{}.xls'.format(datetime.datetime.now())
    book = xlwt.Workbook(encoding='utf-8')
    fields = [
        '用户ID', '手机号', '用户昵称', '来源', '推广', 'sem计划', 'sem单元', 'sem关键词',
        '最后访问时间', '处理时间', '处理人', '结果'
    ]

    sheet = book.add_sheet('客户列表')

    for index, value in enumerate(fields):
        sheet.write(0, index, value)
    # write content
    demand_client_query = DemandClient.query.outerjoin(
        Demand, Demand.id == DemandClient.demand_id
    ).filter(
        DemandClient.deleted_at == 0
    )
    if form.validate():
        # 如果根据客户手机号查找
        if form.user_mobile.data:
            owner = User.query.filter_by(
                mobile=form.user_mobile.data, deleted_at=0).first()
            if owner:
                demand_client_query = demand_client_query.filter(
                    DemandClient.user_id == owner.id
                )
            else:
                demand_client_query = demand_client_query.filter(
                    DemandClient.user_id == 0
                )

        # 如果根据客户昵称查找(手机号优先级比昵称高)
        if form.user_nickname.data and not form.user_mobile.data:
            owner = User.query.filter_by(
                nickname=form.user_nickname.data, deleted_at=0).all()
            if owner:
                user_ids = [i.id for i in owner]
                demand_client_query = demand_client_query.filter(
                    DemandClient.user_id.in_(user_ids)
                )

        if int(form.operate_time_filter.data) > 0:
            dic = {
                '1': (0, 300),
                '2': (300, 600),
                '3': (600, 1200),
                '4': (1200, 1800),
                '5': (1800, 3600),
                '6': (3600,)
            }
            op_time_data = dic[form.operate_time_filter.data]
            demand_client_query = demand_client_query.filter(
                DemandClient.operate_time >= op_time_data[0]
            )
            if len(op_time_data) > 1:
                demand_client_query = demand_client_query.filter(
                    DemandClient.operate_time <= op_time_data[1]
                )

        if form.last_visit_time_begin.data:
            demand_client_query = demand_client_query.filter(
                DemandClient.last_visit_time >= form.last_visit_time_begin.data
            )

        if form.last_visit_time_end.data:
            demand_client_query = demand_client_query.filter(
                DemandClient.last_visit_time <= form.last_visit_time_end.data
            )

        if form.sem_path.data:
            demand_client_query = demand_client_query.filter(
                DemandClient.sem_path.like('%{}%'.format(form.sem_path.data))
            )

        # 表单其他参数处理
        for key, value in form.data.items():
            tmp_form_field = getattr(form, key)
            # 排除提交按钮
            # 排除未设置的form字段，value可能为0
            # 排除为值为 NotChoices 的 NonValidatingSelectField
            ignore_keys = (
                'submit', 'user_mobile', 'user_nickname', 'operate_time_filter',
                'sem_path', 'last_visit_time_begin', 'last_visit_time_end'
            )
            if key in ignore_keys:
                continue
            if value is None or value == '' or value is False:
                continue
            if isinstance(tmp_form_field, NonValidatingSelectField) \
                    and str(value) == str(NotChoices[0][0]):
                continue

            # 根据key的分别对 DemandClient 或 Demand 的col增加查询条件
            if key in ('sem_plan', 'sem_unit', 'sem_keyword'):
                demand_client_query = demand_client_query.filter(
                    getattr(Demand, key) == value
                )
            else:
                demand_client_query = demand_client_query.filter(
                    getattr(DemandClient, key) == value
                )

    data = [
        (
            i.client.id,
            i.client.mobile if i.client.is_engineer else encrypt(i.client.mobile),
            i.client.nickname,
            i.demand_client_source_display,
            i.sem_path,
            i.demand.sem_plan if i.demand else '',
            i.demand.sem_unit if i.demand else '',
            i.demand.sem_keyword if i.demand else '',
            i.last_visit_time,
            i.operate_time_display or '-',
            i.operator.nickname if i.operator else '-',
            i.operate_result_display or '-',
        ) for i in demand_client_query.all()
    ]
    for index, each in enumerate(data):
        for num, value in enumerate(each):
            sheet.write(index+1, num, value)
    book.save(template_path)
    if not os.path.exists(template_path):
        abort(404)
    return send_file(template_path, as_attachment=True)


@op_bp.route('/pending/renewable_demand_list', methods=['GET', 'POST'])
@login_required
def pending_renewable_demand_list():

    demand_query = RenewableDemand.query.join(
        Demand, Demand.id == RenewableDemand.demand_id
    ).filter(
        RenewableDemand.recovery_at > 0,
        RenewableDemand.operated_status == OperatedStatus.NA.value
    ).with_entities(
        Demand,
        RenewableDemand
    )
    page = request.args.get('page', 1, type=int)
    pagination = demand_query.order_by(Demand.created_at.desc()).paginate(
        page, per_page=current_app.config['FLASKY_COMMENTS_PER_PAGE'],
        error_out=False)
    demands = pagination.items

    return render_template(
        'demand/renewable_demand_list.html',
        demands=demands,
        pagination=pagination,
        url='op_bp.pending_renewable_demand_list',
        form_route='op_bp.pending_renewable_demand_list',
        page_title='回收待处理列表',
        url_kwargs=dict()
    )


@op_bp.route('/ajax/demand/operator', methods=['POST'])
@login_required
def ajax_demand_operator():
    """
    create by ld_wu 2018-04-04
    回收订单作废操作
    :return:
    """
    data = request.get_json()
    operated_status = data['operated_status']
    record_id = data['record_id']
    success = 1
    failed = 0
    status = success
    # 只能进行作废操作
    if operated_status != OperatedStatus.INVALID.value:
        status = failed

    renewable_demand = RenewableDemand.get_by_id(record_id)
    if not renewable_demand:
        status = failed
    # 已经处理过
    if renewable_demand.operated_status != OperatedStatus.NA.value:
        # 之前的处理跟当前更新的结果不一样
        if renewable_demand.operated_status != operated_status:
            status = failed
    else:
        renewable_demand.operated_status = operated_status
        renewable_demand.operator_id = current_user.id
        renewable_demand.operator_name = current_user.nickname
    return jsonify(status=status)


@op_bp.route('/training/renewable_demand/<int:record_id>', methods=['GET', 'POST'])
@login_required
def training_renewable_demand(record_id):
    """
    create by ld_wu 2018-04-04
    回收订单赠送操作
    :param record_id:
    :return:
    """
    form = TrainingDemandForm(formdata=request.form or request.args)
    redirect_url = request.args.get('redirect_url')
    if form.validate_on_submit():
        mobiles = form.mobiles.data
        mobile_list = re.split(',|，', mobiles)
        mobile_list = [each.strip() for each in mobile_list if each]
        renewable_demand = RenewableDemand.get_by_id(record_id)
        if renewable_demand and renewable_demand.operated_status == OperatedStatus.NA.value:
            error_mobiles, duplicate_mobiles = renewable_demand.training_demand(mobile_list)
            if duplicate_mobiles or error_mobiles:
                if duplicate_mobiles:
                    flash('以下号码已经抢过单了：{}'.format(', '.join(duplicate_mobiles)))
                if error_mobiles:
                    flash('号码不存在：{}'.format(', '.join(error_mobiles)))

                return render_template(
                    'demand/training_renewable_demand.html',
                    record_id=record_id,
                    form=form,
                    redirect_url=redirect_url
                )
            else:
                renewable_demand.operated_status = OperatedStatus.TRAINING.value
                renewable_demand.operator_id = current_user.id
                renewable_demand.operator_name = current_user.nickname
        return redirect(redirect_url)

    return render_template(
        'demand/training_renewable_demand.html',
        record_id=record_id,
        form=form,
        redirect_url=redirect_url
    )


@op_bp.route('/renewable_demand_list', methods=['GET', 'POST'])
@login_required
def renewable_demand_list():
    form = SearchRenewableDemandForm(formdata=request.form or request.args)

    demand_query = RenewableDemand.query.join(
        Demand, Demand.id == RenewableDemand.demand_id
    ).filter(
        RenewableDemand.recovery_at > 0
    ).with_entities(
        Demand,
        RenewableDemand
    )

    url_kwargs = {}
    if form.validate():

        # 如果根据发布者手机号查找，则根据 relationship 查找 Demand
        if form.user_phone.data:
            owner = User.query.filter_by(
                mobile=form.user_phone.data, deleted_at=0).first()
            if owner:
                demand_query = demand_query.filter(
                    Demand.uid == owner.id
                )
            else:
                demand_query = demand_query.filter(None)

        query_kwargs, url_kwargs = form.generate_kwargs()

        # 根据 form 表单的值，构造Demand的query对象
        demand_query = Demand.generate_list_query_condition(
            args=query_kwargs,
            query=demand_query,
            need_orderby=False
        )

        demand_query = RenewableDemand.generate_list_query_condition(
            args=query_kwargs,
            query=demand_query,
            need_orderby=False
        )
        if form.has_audio.data:
            demand_query = demand_query.filter(
                Demand.audio != ''
            )

    page = request.args.get('page', 1, type=int)
    pagination = demand_query.order_by(Demand.created_at.desc()).paginate(
        page, per_page=current_app.config['FLASKY_COMMENTS_PER_PAGE'],
        error_out=False)
    demands = pagination.items

    return render_template(
        'demand/renewable_demand_list.html',
        has_search=True,
        form=form,
        demands=demands,
        pagination=pagination,
        url_kwargs=url_kwargs,
        url='op_bp.renewable_demand_list',
        form_route='op_bp.renewable_demand_list',
        page_title='订单回收站'
    )


@op_bp.route('/export/renewable_demand_list')
@login_required
def export_renewable_demand_list():
    form = SearchRenewableDemandForm(formdata=request.form or request.args)
    template_path = '/tmp/renewable_demand_list_{}.xls'.format(datetime.datetime.now())
    book = xlwt.Workbook(encoding='utf-8')
    fields = [
        '项目ID', '用户ID', '手机号', '用户昵称', '需求分类', '故障', '类型',
        '环境', '大小', '颜色', '间距', '省份', '城市', '状态',
        '客服', '发布次数', '创建时间', '发布时间', '处理人', '处理结果',
        '成功人数', '重发成功人数'
    ]
    sheet = book.add_sheet('回收站订单详情')
    for index, item in enumerate(fields):
        sheet.write(0, index, item)

    demand_query = RenewableDemand.query.join(
        Demand, Demand.id == RenewableDemand.demand_id
    ).filter(
        RenewableDemand.recovery_at > 0
    ).with_entities(
        Demand,
        RenewableDemand
    )

    if form.validate():
        # 如果根据发布者手机号查找，则根据 relationship 查找 Demand
        if form.user_phone.data:
            owner = User.query.filter_by(
                mobile=form.user_phone.data, deleted_at=0).first()
            if owner:
                demand_query = demand_query.filter(
                    Demand.uid == owner.id
                )
            else:
                demand_query = demand_query.filter(None)

        query_kwargs, url_kwargs = form.generate_kwargs()

        # 根据 form 表单的值，构造Demand的query对象
        demand_query = Demand.generate_list_query_condition(
            args=query_kwargs,
            query=demand_query,
            need_orderby=False
        )

        demand_query = RenewableDemand.generate_list_query_condition(
            args=query_kwargs,
            query=demand_query,
            need_orderby=False
        )
        if form.has_audio.data:
            demand_query = demand_query.filter(
                Demand.audio != ''
            )

    data = [
        (
            demand.id,
            demand.owner.id if demand.owner else '',
            demand.owner.mobile if demand.owner and demand.owner.is_engineer else encrypt(
                demand.owner and demand.owner.mobile or ''),
            demand.owner.nickname if demand.owner else '',
            demand.cate_display,
            demand.malf_display,
            demand.type_display,
            demand.surr_display,
            demand.size,
            demand.color_display,
            demand.span,
            demand.prov,
            demand.city,
            demand.status_display,
            demand.admin_display,
            demand.published_count,
            demand.created_datetime,
            demand.published_datetime,
            renewable_demand.operator_name,
            renewable_demand.operated_status_display,
            renewable_demand.success_count,
            renewable_demand.recovery_success_count
        )for demand, renewable_demand in demand_query.order_by(
            RenewableDemand.created_at.desc())
    ]
    for index, each in enumerate(data):
        for num, value in enumerate(each):
            sheet.write(index+1, num, value)
    book.save(template_path)
    return send_file(template_path, as_attachment=True)


@op_bp.route('/user_visit_demand/<int:demand_id>', methods=['GET'])
@login_required
def user_visit_demand(demand_id):
    demand = Demand.query.get_or_404(demand_id)
    records = UserVisitDemand.query.filter_by(
        demand_id=demand.id
    ).order_by(
        UserVisitDemand.created_datetime.asc()
    )
    return render_template(
        'demand/user_visit_demand.html',
        demand=demand,
        records=records,
    )
